package com.example.spring.mapper;

import com.example.spring.entity.Good;
import com.example.spring.entity.History;
import com.example.spring.entity.Record;
import net.sf.jsqlparser.expression.DateTimeLiteralExpression;
import org.apache.ibatis.annotations.*;

import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

@Mapper
public interface GoodMapper {

    //指定仓库当前货物总量
    @Select("SELECT COUNT(*) FROM good, slot WHERE good.sid = slot.sid AND slot.whid = #{whid} AND good.sid != 0")
    Integer findByWhidTotal(@Param("whid") Integer whid) ;

    @Select("SELECT * FROM good")
    List<Good> findAll();

    @Insert("INSERT INTO good(status, type, name, info, pic) VALUES(#{status}," +
            "#{type}, #{name}, #{info}, #{pic})")
    int insert_data(Good good);

    int update(Good good);

    @Delete("DELETE FROM good WHERE gid = #{gid}")
    Integer deleteById(@Param("gid") Integer gid);

    //指定whid，查每种类型的有多少个
    @Select("SELECT COUNT(*) FROM slot AS s, good AS g " +
            "WHERE g.sid = s.sid AND g.sid != 0 AND "+
            "s.whid = #{whid} GROUP BY g.type " +
            "ORDER BY g.type")
    List<Integer> findByWhidType(@Param("whid") Integer whid);

    //指定whid，查询货物种类
    @Select("SELECT DISTINCT type FROM good AS g, slot AS s WHERE s.whid = #{whid} AND type IS NOT NULL  " +
            "AND g.sid != 0 ORDER BY type")
    List<String> findWhType(@Param("whid") Integer whid);

    //指定whid和type，分页查询商品
    @Select("SELECT * FROM good AS g, slot AS s WHERE g.sid = s.sid " +
            "AND s.whid = #{whid} AND g.type = #{type} " +
            "AND g.sid != 0 LIMIT #{pageNum}, #{pageSize}")
    List<Good> findByType_page(@Param("whid") Integer whid, @Param("type") String type,
                                 @Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize);

    //指定whid，查询某类货物总数
    @Select("SELECT COUNT(*) FROM good AS g, slot AS s WHERE g.sid = s.sid " +
            "AND s.whid = #{whid} AND g.type = #{type} AND g.sid != 0")
    Integer findByType_Total(@Param("whid") Integer whid, @Param("type") String type);

    //指定sid，查询该货架上的商品总数
    @Select("SELECT COUNT(*) FROM good WHERE sid = #{sid}")
    Integer findBySlot_Total(@Param("sid") Integer sid);

    //给定gid查找当前sid
    @Select("SELECT sid FROM good WHERE gid = #{gid} ")
    Integer findSlotId(@Param("gid") Integer gid);
    //给定gid查找whid
    @Select("SELECT whid FROM slot WHERE sid = #{sid}")
    Integer findWhid(@Param("sid") Integer sid);



    //以下是机器人发送到历史信息
    @Insert("INSERT INTO history(whid, ip, acceptStatus, writenInStatus, dataNo, create_time) VALUES(#{whid}, " +
            "#{ip}, #{acceptStatus}, #{writenInStatus}, #{dataNo}, #{createTime})")
    int insert_history(History history);


    @Select("SELECT * FROM history WHERE whid = #{whid} LIMIT #{pageNum}, #{pageSize}")
    List<History> showHistoryPage(@Param("whid") Integer whid, @Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize);

    @Select("SELECT COUNT(*) FROM history WHERE whid = #{whid}")
    Integer showHistoryTotal(@Param("whid") Integer whid);

}
